package dao.impl;

import dao.ZoneDao;
import dao.dto.ZoneDto;
import boerrors.ZoneExistantException;
import boerrors.ZoneInexistantException;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;


public class ZoneDaoImpl extends ZoneDao {
        private static ZoneDao _instance = null;
        private CallableStatement _callableStatement = null;
        private SQLConnection _connection = new SQLConnection();
        
        //Constructeur du singleton
	public static ZoneDao getInstance(){
		if(_instance == null)
			_instance = new ZoneDaoImpl();
		return _instance;
	}
        
        //Methodes
	@Override
	public Vector<ZoneDto> lister() throws ZoneInexistantException {
                ResultSet resultat = null;
                ZoneDto zone = null;
                try {                                      
                        //query
                        _callableStatement = _connection.getConnection().prepareCall("{call sp_ZoneList}");
                        //execute
                        resultat = _callableStatement.executeQuery();
                        Vector<ZoneDto> liste = new Vector<ZoneDto>();
                        while (resultat.next()) {
                                zone = new ZoneDto(resultat.getInt("idZone"),resultat.getString("libZone"));
                                liste.add(zone);
                        }
                        return liste;
                } catch(SQLException e) { 
                    throw new ZoneInexistantException(e);
                } finally { 
                    if (resultat != null) try { resultat.close(); } catch(Exception e) { throw new ZoneInexistantException(e);}
                    if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {throw new ZoneInexistantException(e);}
                    _connection.deconnecter();
                }
        }
        
        //Methodes   
        @Override
        public Vector<ZoneDto> rechercher(String idZone,String libZone) throws ZoneInexistantException{
                ResultSet resultat = null;
                ZoneDto zone = null;
                try {                                      
                        //query
                        _callableStatement = _connection.getConnection().prepareCall("{call sp_ZoneSearchBy(?,?)}");
                        //parameter
                        _callableStatement.setString("idZone",idZone);
                        _callableStatement.setString("libZone",libZone);
                        //execute
                        resultat = _callableStatement.executeQuery();
                        Vector<ZoneDto> liste = new Vector<ZoneDto>();
                        while (resultat.next()) {
                                zone = new ZoneDto(resultat.getInt("idZone"),resultat.getString("libZone"));
                                liste.add(zone);
                        }
                        return liste;
                } catch(SQLException e) { 
                    throw new ZoneInexistantException(e);
                } finally { 
                    if (resultat != null) try { resultat.close(); } catch(Exception e) { throw new ZoneInexistantException(e);}
                    if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {throw new ZoneInexistantException(e);}
                    _connection.deconnecter();
                }
        }
        
        /*
        @Override
	public ZoneDto rechercher(long idZone) throws ZoneInexistantException {
                ResultSet resultat = null;
                try {
                        //query
                        _callableStatement = _connection.getConnection().prepareCall("{call sp_ZoneSelectIdZone(?)}");
                        //parameter
                        _callableStatement.setLong("idZone",idZone);
                        //execute
                        resultat = _callableStatement.executeQuery();    
                	if(!resultat.next()) throw new ZoneInexistantException();
			return new ZoneDto(resultat.getInt("IDZONE"), resultat.getString("LIBELLEZONE"));
                }catch(SQLException e){throw new ZoneInexistantException(e);
                }finally{           
                        if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {}
                        _connection.deconnecter();}
	}

        @Override
	public ZoneDto rechercher(String libelleZone) throws ZoneInexistantException {
		ResultSet resultat = null;
                try {
                        //query
                        _callableStatement = _connection.getConnection().prepareCall("{call sp_ZoneSelectLibelleZone(?)}");
                        //parameter
                        _callableStatement.setString("libelleZone",libelleZone);
                        //execute
                        resultat = _callableStatement.executeQuery();    
                	if(!resultat.next()) throw new ZoneInexistantException();
			return new ZoneDto(resultat.getInt("IDZONE"), resultat.getString("LIBELLEZONE"));
                }catch(SQLException e){throw new ZoneInexistantException(e);
                }finally{
                        if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {}
                        _connection.deconnecter();}
        }    
            
        @Override
	public ZoneDto rechercher(long idZone, String libelleZone) throws ZoneInexistantException {
		ResultSet resultat = null;
                try {
                        //query
                        _callableStatement = _connection.getConnection().prepareCall("{call sp_ZoneSelectIdLibelle(?,?)}");
                        //parameter
                        _callableStatement.setLong("idZone",idZone);
                        _callableStatement.setString("libelleZone",libelleZone);
                        //execute
                        resultat = _callableStatement.executeQuery();    
                	if(!resultat.next()) throw new ZoneInexistantException();
			return new ZoneDto(resultat.getInt("IDZONE"), resultat.getString("LIBELLEZONE"));
                }catch(SQLException e){throw new ZoneInexistantException(e);
                }finally{
                        if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {}
                        _connection.deconnecter();}
        }    
                       
        @Override
	public void enregistrer(ZoneDto zone) throws ZoneExistantException {
                try {
                        //query
                        _callableStatement = _connection.getConnection().prepareCall("{call sp_ZoneInsert(?,?)}");
                        //parameter
                        _callableStatement.setLong("idZone",zone.getIdZone());
                        _callableStatement.setString("libelleZone",zone.getLibelleZone());
                        //execute
                        _callableStatement.executeUpdate();    
                }catch(SQLException e){throw new ZoneExistantException(e);
                }finally{
                        if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {}
                        _connection.deconnecter();}
        }
        
        @Override
	public void modifier(ZoneDto zone) throws ZoneInexistantException {
                try {
                        //query
                        _callableStatement = _connection.getConnection().prepareCall("{call sp_ZoneUpdate(?,?)}");
                        //parameter
                        _callableStatement.setLong("idZone",zone.getIdZone());
                        _callableStatement.setString("libelleZone",zone.getLibelleZone());
                        //execute
                        _callableStatement.executeUpdate();    
                }catch(SQLException e){throw new ZoneInexistantException(e);
                }finally{
                        if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {}
                        _connection.deconnecter();}
        }
        
        @Override
	public void supprimer(ZoneDto zone) throws ZoneInexistantException {
                try {
                        //query
                        _callableStatement = _connection.getConnection().prepareCall("{call sp_ZoneDelete(?)}");
                        //parameter
                        _callableStatement.setLong("idZone",zone.getIdZone());
                        //execute
                        _callableStatement.executeUpdate();    
                }catch(SQLException e){throw new ZoneInexistantException(e);
                }finally{
                        if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {}
                        _connection.deconnecter();}
        }
         */ 
         
}